package carassius.DAL;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.AbstractMap.SimpleEntry;
import java.util.ArrayList;
import java.util.Collection;

/**
 *
 * @author siebz0r
 */
public class OptimalOrganismWaterConditionTable
		extends Table<OptimalOrganismWaterConditionRow, SimpleEntry<Integer, String>>
{
	private final String SQL_ADD = "INSERT INTO optimal_organism_water_condition VALUES ( ? , ? , ? , ? )",
			SQL_DELETE = "DELETE FROM optimal_organism_water_condition WHERE organism_id = ? AND water_condition = ?",
			SQL_DELETE_BY_ORGANISM_ID = "DELETE FROM optimal_organism_water_condition WHERE organism_id = ?",
			SQL_DELETE_BY_CONDITION = "DELETE FROM optimal_organism_water_condition WHERE water_condition = ?",
			SQL_GET_ALL = "SELECT * FROM optimal_organism_water_condition",
			SQL_GET_BY_ORGANISM_ID = "SELECT * FROM optimal_organism_water_condition WHERE organism_id = ?",
			SQL_GET_BY_CONDITION_NAME = "SELECT * FROM optimal_organism_water_condition WHERE condition = ?",
			SQL_GET_BY_ORGANISM_ID_AND_CONDITION_NAME = "SELECT * FROM optimal_organism_water_condition WHERE organism_id = ? AND water_condition = ?";
	private boolean _allCached;

	//<editor-fold defaultstate="collapsed" desc="Public Methods">
	//<editor-fold defaultstate="collapsed" desc="SQL add">
	public OptimalOrganismWaterConditionRow add(OrganismRow organismRow, WaterConditionRow waterConditionRow, Double minValue, Double maxValue) throws SQLException
	{
		if (organismRow != null && waterConditionRow != null)
		{
			return add(organismRow.getId(), waterConditionRow.getName(), minValue, maxValue);
		}
		return null;
	}

	public OptimalOrganismWaterConditionRow add(Integer organismID, String waterCondition, Double minValue, Double maxValue) throws SQLException
	{
		SimpleEntry<Integer, String> keys = add(new Object[]{organismID, waterCondition, minValue, maxValue});
		if (keys != null)
		{
			return getByPrimaryKey(keys);
		}
		return null;
	}

	@Override
	public SimpleEntry<Integer, String> add(Object[] args) throws SQLException
	{
		SimpleEntry<Integer, String> out = null;
		if (args != null
				&& args.length >= 4
				&& ((args[0] instanceof OrganismRow && ((OrganismRow) args[0]).getId() >= 0)
				|| (args[0] instanceof Integer && ((Integer) args[0]) >= 0))
				&& ((args[1] instanceof String && !((String) args[1]).isEmpty())
				|| (args[1] instanceof WaterConditionRow && (((WaterConditionRow) args[1]).getName() != null && !((WaterConditionRow) args[1]).getName().isEmpty())))
				&& (args[2] instanceof Double || args[3] instanceof Double))
		{
			out = new SimpleEntry<Integer, String>((args[0] instanceof Integer)
					? (Integer) args[0]
					: ((OrganismRow) args[0]).getId(),
					(args[1] instanceof String)
					? ((String) args[1]).trim()
					: ((WaterConditionRow) args[1]).getName().trim());

			PreparedStatement st = Database.prepareStatement(SQL_ADD);
			st.setObject(1, out.getKey(), Types.INTEGER);
			st.setString(2, out.getValue());
			st.setObject(3, (args[2] instanceof Double) ? args[2] : null, Types.DOUBLE);
			st.setObject(4, (args[3] instanceof Double) ? args[3] : null, Types.DOUBLE);

			synchronized (getRows())
			{
				try
				{
					if (st.executeUpdate() == 1)
					{
						_allCached = false;
						getByPrimaryKey(out);
						_allCached = true;
					}
					else
					{
						out = null;
					}
				}
				catch (SQLException ex)
				{
					throw ex;
				}
				finally
				{
					st.close();
				}
			}
		}
		return out;
	}
	//</editor-fold>

	//<editor-fold defaultstate="collapsed" desc="SQL delete">
	@Override
	public boolean delete(SimpleEntry<Integer, String> keys) throws SQLException
	{
		boolean result = false;
		if (keys != null
				&& keys.getKey() != null
				&& keys.getKey() >= 0
				&& keys.getValue() != null
				&& !keys.getValue().isEmpty())
		{
			PreparedStatement st = Database.prepareStatement(SQL_DELETE);
			st.setInt(1, keys.getKey());
			st.setString(2, keys.getValue());

			synchronized (getRows())
			{
				try
				{
					if (st.executeUpdate() == 1)
					{
						result = true;
						getRows().remove(keys);
					}
				}
				catch (SQLException ex)
				{
					throw ex;
				}
				finally
				{
					st.close();
				}
			}
		}
		return result;
	}

	public boolean deleteByOrganismID(int id) throws SQLException
	{
		boolean result = false;
		if (id >= 0)
		{
			PreparedStatement st = Database.prepareStatement(SQL_DELETE_BY_ORGANISM_ID);
			st.setInt(1, id);

			synchronized (getRows())
			{
				if (st.executeUpdate() > 0)
				{
					result = true;
					if (getRows() != null && !getRows().isEmpty())
					{
						for (OptimalOrganismWaterConditionRow row : getRows().values())
						{
							if (row.getOrganismId() == id)
							{
								getRows().remove(row.getPrimaryKeys());
							}
						}
					}
				}
			}
		}
		return result;
	}

	public boolean deleteByCondition(String name) throws SQLException
	{
		boolean result = false;
		if (name != null && !name.trim().isEmpty())
		{
			PreparedStatement st = Database.prepareStatement(SQL_DELETE_BY_CONDITION);
			st.setString(1, name.trim());

			synchronized (getRows())
			{
				try
				{
					if (st.executeUpdate() > 0)
					{
						result = true;
						if (getRows() != null && !getRows().isEmpty())
						{
							for (OptimalOrganismWaterConditionRow row : getRows().values())
							{
								if (row.getCondition().getName().equals(name))
								{
									getRows().remove(row.getPrimaryKeys());
								}
							}
						}
					}
				}
				catch (SQLException ex)
				{
					throw ex;
				}
				finally
				{
					st.close();
				}
			}
		}
		return result;
	}
	//</editor-fold>

	//<editor-fold defaultstate="collapsed" desc="SQL get">
	@Override
	public Collection<OptimalOrganismWaterConditionRow> getAll() throws SQLException
	{
		PreparedStatement st = Database.prepareStatement(SQL_GET_ALL);

		synchronized (getRows())
		{
			if (!_allCached)
			{
				try
				{
					ResultSet query = st.executeQuery();

					while (query.next())
					{
						OptimalOrganismWaterConditionRow optimalOrganismWaterConditionRow = new OptimalOrganismWaterConditionRow(query);
						put(optimalOrganismWaterConditionRow);
					}
					_allCached = true;
				}
				catch (SQLException ex)
				{
					throw ex;
				}
				finally
				{
					st.close();
				}
			}
			return getRows().values();
		}
	}

	public Collection<OptimalOrganismWaterConditionRow> getByOrganismId(int id) throws SQLException
	{
		ArrayList<OptimalOrganismWaterConditionRow> rowsByOrganism = null;
		if (id >= 0)
		{
			rowsByOrganism = new ArrayList<OptimalOrganismWaterConditionRow>();
			PreparedStatement st = Database.prepareStatement(SQL_GET_BY_ORGANISM_ID);
			st.setInt(1, id);

			try
			{
				synchronized (getRows())
				{
					if (_allCached)
					{
						for (OptimalOrganismWaterConditionRow optimalOrganismWaterConditionRow : getRows().values())
						{
							if (optimalOrganismWaterConditionRow.getOrganismId() == id)
							{
								rowsByOrganism.add(optimalOrganismWaterConditionRow);
							}
						}
					}
					else
					{
						ResultSet query = st.executeQuery();

						while (query.next())
						{
							OptimalOrganismWaterConditionRow optimalOrganismWaterConditionRow = new OptimalOrganismWaterConditionRow(query);
							put(optimalOrganismWaterConditionRow);
							rowsByOrganism.add(optimalOrganismWaterConditionRow);
						}
					}
				}
			}
			catch (SQLException ex)
			{
				throw ex;
			}
			finally
			{
				st.close();
			}
		}
		return rowsByOrganism;
	}

	public Collection<OptimalOrganismWaterConditionRow> getByConditionName(
			String name) throws SQLException
	{
		ArrayList<OptimalOrganismWaterConditionRow> rowsByCondition = null;
		if (name != null && !name.isEmpty())
		{
			rowsByCondition = new ArrayList<OptimalOrganismWaterConditionRow>();

			PreparedStatement st = Database.prepareStatement(SQL_GET_BY_CONDITION_NAME);
			st.setString(1, name.trim());

			try
			{
				synchronized (getRows())
				{
					if (_allCached)
					{
						for (OptimalOrganismWaterConditionRow optimalOrganismWaterConditionRow : getRows().values())
						{
							rowsByCondition.add(optimalOrganismWaterConditionRow);
						}
					}
					else
					{
						ResultSet query = st.executeQuery();

						while (query.next())
						{
							OptimalOrganismWaterConditionRow optimalOrganismWaterConditionRow = new OptimalOrganismWaterConditionRow(query);
							put(optimalOrganismWaterConditionRow);
							rowsByCondition.add(optimalOrganismWaterConditionRow);
						}
					}
				}
			}
			catch (SQLException ex)
			{
				throw ex;
			}
			finally
			{
				st.close();
			}
		}
		return rowsByCondition;
	}

	@Override
	public OptimalOrganismWaterConditionRow getByPrimaryKey(
			SimpleEntry<Integer, String> keys) throws SQLException
	{
		OptimalOrganismWaterConditionRow row = null;
		if (keys.getKey() != null
				&& keys.getKey() >= 0
				&& keys.getValue() != null
				&& !keys.getValue().isEmpty())
		{
			synchronized (getRows())
			{
				row = getRows().get(keys);
				if (row != null || _allCached)
				{
					return row;
				}

				PreparedStatement st = Database.prepareStatement(SQL_GET_BY_ORGANISM_ID_AND_CONDITION_NAME);
				st.setInt(1, keys.getKey());
				st.setString(2, keys.getValue());

				try
				{
					ResultSet query = st.executeQuery();

					if (query.next())
					{
						row = new OptimalOrganismWaterConditionRow(query);
						put(row);
					}
				}
				catch (SQLException ex)
				{
					throw ex;
				}
				finally
				{
					st.close();
				}
			}
		}
		return row;
	}
	//</editor-fold>
	//</editor-fold>
}
